﻿using Anke.SHManage.IDAL;
using Anke.SHManage.Model;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Anke.SHManage.MSSQLDAL
{
    public partial class I_InvoiceApplyDAL
    {
        IDALContext dalContext = new DALContextFactory().GetDALContext();
        AKV7ManageEntities db = DBContextFactory.GetDBContext() as AKV7ManageEntities;
        //查询申请列表
        public object GetApplyList(int page, int rows, DateTime start, DateTime end, string state, string center,
                                   string station, E_StatisticsPermisson em, string selfCenterID, string selfStationID)
        {
            List<I_InvoiceApplyInfo> list = new List<I_InvoiceApplyInfo>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"  select   申领编码=ApplyID,分中心编码=CenterCode,分中心=tc.名称,分站编码=StationCode
                                   ,分站=ts.名称,申领人编码=ApplyUserID,申领人=ApplyUserName,申领日期=Convert(varchar(10),ApplyTime,121)  
                                   ,申领数量=ApplyCount,申领状态=ApprovalState,备注1=iia.Remark1,审批人编码=ApprovalUserID
                                   ,审批人=ApprovalUserName,审批数量=ApprovalCount,审批日期=Convert(varchar(10),ApprovalTime,121),
                                   领用日期=Convert(varchar(10),ReceiveTime,121) ,起始号码=StartNumber,截止号码=EndNumber,备注2=iia.Remark2
                          into #tempa
                          from  I_InvoiceApply iia                        
                          left join P_User pu on pu.ID=iia.ApplyUserID ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on iia.StationCode=ts.编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on iia.CenterCode=tc.编码 ");
            sb.Append(" where  iia.ApplyTime >='" + start + "'  and iia.ApplyTime<'" + end + "' ");
            if (state == "-1")
            { sb.Append(" and 1=1 "); }
            if (state == "0")
            { sb.Append(" and iia.ApprovalState='申请中' "); }
            if (state == "1")
            { sb.Append(" and iia.ApprovalState='已审批' "); }

            if (em == E_StatisticsPermisson.None)
                return null;

            else
            {
                if (em == E_StatisticsPermisson.ALL)
                {
                    WhereClauseUtility.AddStringEqual("iia.CenterCode", center, sb);
                    WhereClauseUtility.AddStringEqual("iia.StationCode", station, sb);
                }
                else if (em == E_StatisticsPermisson.CENTER)
                {
                    WhereClauseUtility.AddStringEqual("iia.CenterCode", selfCenterID, sb);
                }
                else if (em == E_StatisticsPermisson.STATION)
                {
                    WhereClauseUtility.AddStringEqual("iia.StationCode", selfStationID, sb);
                }
                sb.Append(@"  
                order by iia.ApplyTime  desc              
                select identity(int,1,1) as 行号,* into #temp from #tempa ");
                sb.Append(@"
                select top " + rows + " A.*  from #temp A where 行号>" + (page - 1) * rows + " order by 行号 ");
                sb.Append(@"
                SELECT count(*) FROM #temp t
                drop table #tempa,#temp ");

                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        I_InvoiceApplyInfo info = new I_InvoiceApplyInfo();
                        info.ApplyID = dr["申领编码"].ToString();
                        info.CenterCode = Convert.ToInt32(dr["分中心编码"]);
                        info.Center = dr["分中心"].ToString();
                        info.StationCode = dr["分站编码"].ToString();
                        info.Station = dr["分站"].ToString();
                        info.ApplyUserID = dr["申领人编码"].ToString();
                        info.ApplyPerson = dr["申领人"].ToString();
                        info.ApplyCount = Convert.ToInt32(dr["申领数量"]);
                        info.ApplyTime = dr["申领日期"].ToString();
                        info.ApprovalState = dr["申领状态"].ToString();
                        info.ApprovalCount = Convert.ToInt32(dr["审批数量"]);
                        info.ApprovalPerson = dr["审批人"].ToString();
                        info.ApprovalUserID = dr["审批人编码"].ToString();
                        info.ApprovalTime = dr["审批日期"].ToString();
                        info.ReceiveTime = dr["领用日期"].ToString();
                        info.StartNumber = dr["起始号码"].ToString();
                        info.EndNumber = dr["截止号码"].ToString();
                        info.Remark1 = dr["备注1"].ToString();
                        info.Remark2 = dr["备注2"].ToString();
                        list.Add(info);
                    }
                }
                int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                var result = new { total = total, rows = list };
                return result;

            }
        }


        #region 发票异地情况统计表  2016-4-11 
        public DataTable getInvoiceNumberAbnormalUse(DateTime beginTime, DateTime endTime, string txtCenter,string stationID)
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            StringBuilder sb = new StringBuilder();

            sb.Append(@" 
                select  ia.CenterCode,ia.StartNumber,ia.EndNumber,ia.ApplyTime ,ia.ApplyUserName,tc.名称  as CenterName,ia.StationCode
                        ,ts.名称 as StationName
                from I_InvoiceApply ia   ");

            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on tc.编码=ia.CenterCode ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on ts.编码=ia.StationCode ");
            sb.Append("  where 1= 1  ");
          
            WhereClauseUtility.AddDateTimeGreaterThan("ia.ApplyTime", beginTime, sb);
            WhereClauseUtility.AddDateTimeLessThan("ia.ApplyTime", endTime, sb);
            WhereClauseUtility.AddStringEqual("ia.CenterCode", txtCenter, sb);
            WhereClauseUtility.AddStringEqual("ia.StationCode", stationID, sb);
            sb.Append("  and ia.StartNumber is not null  ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            //先将条件时间内的发票申领内容放进tempApplyList 表中
            DataTable tempApplyList = ds.Tables[0];
            //一共有rowCount 条数据，对tempApplyList进行循环操作rowCount 次，
            int rowCount = tempApplyList.Rows.Count > 0 ? tempApplyList.Rows.Count : 0;

            //最后总的汇总到这个total 表里
            DataTable total = new DataTable();
            total.Columns.Add("发票号");
            total.Columns.Add("使用分中心");
            total.Columns.Add("实用人");
            total.Columns.Add("领用时间");
            total.Columns.Add("领用分中心");
            total.Columns.Add("申领人");
            total.Columns.Add("使用分站");
            total.Columns.Add("领用分站");
            //将Apply表的内容遍历一遍,将
            for (int a = 0; a < rowCount; a++)
            {
                string ApplyCenterCode = tempApplyList.Rows[a]["CenterCode"].ToString();
                string beginPage = tempApplyList.Rows[a]["StartNumber"].ToString();
                string endPage = tempApplyList.Rows[a]["EndNumber"].ToString();
                string ApplyCenterName = tempApplyList.Rows[a]["CenterName"].ToString();
                string ApplyUserName = tempApplyList.Rows[a]["ApplyUserName"].ToString();
                string ApplyTime = tempApplyList.Rows[a]["ApplyTime"].ToString();

                string ApplyStationCode = tempApplyList.Rows[a]["StationCode"].ToString();
                string ApplyStationName = tempApplyList.Rows[a]["StationName"].ToString();
                //DateTime ApplyTime = Convert.ToDateTime(tempApplyList.Rows[a]["ApplyTime"]);

                StringBuilder sb1 = new StringBuilder();

                //以下段SQL取的是病例使用发票号 段在beginPage--endPage中，但是出车任务中心编码不在ApplyCenterCode（申领发票的分中心）中的数据 存入temp 表
                sb1.Append(@"
                          select 
                             pcl.InvoiceNumber as 发票号,tc.名称 as 使用分中心,pr.AgentName as 实用人,'" + ApplyTime + @"' as 领用时间 
                             ,'" + ApplyCenterName + @"' as 领用分中心, '" + ApplyUserName + @"' as 申领人 
                             ,ts.名称 as 使用分站,'" + ApplyStationName + @"' as 领用分站
                            from [dbo].[M_PatientChargeLog] pcl  
                            left join  M_PatientRecord pr  on pcl.TaskCode = pr.TaskCode  ");

                sb1.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation  ts on pr.OutStationCode=ts.编码  ");
                sb1.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on tc.编码=ts.中心编码  ");

                sb1.Append("    where InvoiceNumber >=" + beginPage + " and InvoiceNumber<=" + endPage + @"   
                             and   ts.中心编码 <> " + ApplyCenterCode + @"   and  ts.中心编码  is not null
                             and ts.编码 <> " + ApplyStationCode + @"   and  ts.编码  is not null ");
                DataSet ds1 = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb1.ToString(), null);
                DataTable temp = ds1.Tables[0];

                int tempCount = temp.Rows.Count > 0 ? temp.Rows.Count : 0;
                //将temp表的所有内容遍历一遍，存进最后的total中
                for (int i = 0; i < tempCount; i++)
                {
                    DataRow dr = total.NewRow();
                    dr["发票号"] = temp.Rows[i]["发票号"].ToString();
                    dr["使用分中心"] = temp.Rows[i]["使用分中心"].ToString();
                    dr["实用人"] = temp.Rows[i]["实用人"].ToString();
                    dr["领用时间"] = temp.Rows[0]["领用时间"].ToString();
                    dr["领用分中心"] = temp.Rows[0]["领用分中心"].ToString();//保养时间
                    dr["申领人"] = temp.Rows[0]["申领人"].ToString(); //保养内容

                    dr["使用分站"] = temp.Rows[i]["使用分站"].ToString();
                    dr["领用分站"] = temp.Rows[0]["领用分站"].ToString();

                    total.Rows.Add(dr);

                }

            }
             
            return total;
        }
        #endregion
    }
}
